//
//  NewsDataBaseHandle.m
//  MusicNews
//
//  Created by lanou3g on 15/10/15.
//  Copyright © 2015年 LC.J. All rights reserved.
//

#import "NewsDataBaseHandle.h"

@implementation NewsDataBaseHandle
//
//  NewsDataBaseHandle.m
//  NEWS_W_Y_S
//
//  Created by lanou3g on 15/10/14.
//  Copyright © 2015年 lanou3g.jake. All rights reserved.
//

#import "NewsDataBaseHandle.h"
@end
@implementation NewsDataBaseHandle

static NewsDataBaseHandle *defaultHandle = nil;

+ (NewsDataBaseHandle *)sharedInstance
{
    @synchronized(self){
        if (defaultHandle == nil) { // 加了一个死锁保护，保证同时只有一个程序访问该方法
            defaultHandle = [[NewsDataBaseHandle alloc] init];
        }
        return defaultHandle;
    }
}

#pragma mark - 创建数据库
static sqlite3 *db = nil;
- (sqlite3 *)openDB
{
    if (db != nil) {
        return db;
    }
    NSString *cachesPath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqliteFilePath = [cachesPath stringByAppendingPathComponent:@"NewsInfo.sqlist"];
    NSLog(@"数据库路径：%@", sqliteFilePath);
    int result = sqlite3_open([sqliteFilePath UTF8String], &db);
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库打开成功");
        NSString *sql = @"create table NewsInfo(title text primary key not NULL, newsData data not NULL)";
        char *error = nil;
        int splResult = sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error); //
        if (splResult == SQLITE_OK) {
            NSLog(@"数据库表创建成功");
        }else{
            NSLog(@"创建表失败ERROR = %s", error);
        }
    }else
    {
        NSLog(@"数据库打开失败");
    }
    return db;
}

- (void)closeDB
{
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        db = nil;
    }
}

#pragma mark - 插入数据
- (void)insertNewsInfo:(NSString*)title newsData:(NSData*)data
{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into NewsInfo(title, newsData) values(?, ?)"];
    NSLog(@"insertSQL = %@", insertSQL);
    sqlite3_stmt *stmt = NULL;
    int result1 = sqlite3_prepare_v2(db, [insertSQL UTF8String], -1, &stmt, NULL);
    if (result1 == SQLITE_OK) {
        NSLog(@"语法检查正确");
        sqlite3_bind_text(stmt, 1, [title UTF8String], -1, nil);
        sqlite3_bind_blob(stmt, 2, [data bytes], (int)[data length], nil);
        sqlite3_step(stmt);
        NSLog(@"插入数据成功");
    }else{
        NSLog(@"语法检查错误");
    }
}

#pragma mark - 取出数据
- (NSData*)searchAllNews:(NSString*)title
{
    NSData *data = [NSData data];
    NSString *selectSQL = @"select * from NewsInfo where title = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(db, [selectSQL UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"语法检查正确");
        sqlite3_bind_text(stmt, 1, [title UTF8String], -1, nil);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int bytes = sqlite3_column_bytes(stmt, 1);
            Byte * value = (Byte*)sqlite3_column_blob(stmt, 1);
            if (bytes !=0 && value != NULL)
            {
                
                data = [NSData dataWithBytes:value length:bytes];
                
            }
        }
        sqlite3_finalize(stmt);
        return data;
        
    }else{
        NSLog(@"语法检查错误");
        
    }
    // 9.释放所有权
    sqlite3_finalize(stmt);
    return nil;
}

#pragma mark - 更新数据
- (void)upDatesearchAllNews:(NSString*)title newsData:(NSData*)data
{
    NSString *updateSQL = [NSString stringWithFormat:@"update NewsInfo set newsData = '%@' where title = '%@'", data, title];
    // 2.执行SQL语句
    sqlite3_stmt *stmt = NULL;
    char *error = nil;
    int result = sqlite3_prepare_v2(db, [updateSQL UTF8String], -1, &stmt, nil);
    // 3.判断SQL语句是否执行成功
    if (result == SQLITE_OK) {
        NSLog(@"更新成功");
    }else
    {
        NSLog(@"更新失败： %s", error);
    }
}

#pragma mark - 查询数据
- (BOOL)searchNewsTitle:(NSString*)title
{
    NSString *selectSQL = @"select * from NewsInfo where title = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(db, [selectSQL UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_text(stmt, 1, [title UTF8String], -1, nil);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            char *name_C = (char*)sqlite3_column_text(stmt, 0);
            NSString *string = [NSString stringWithUTF8String:name_C];
            if ([string isEqualToString:title]) {
                return YES;
            }
            
        }
    }
    return NO;
}

#pragma mark - 创建存放标题栏目的表格
static sqlite3 *dbA = nil;
- (sqlite3 *)openDB1
{
    if (dbA != nil) {
        return dbA;
    }
    NSString *cachesPath = [NSSearchPathForDirectoriesInDomains(NSCachesDirectory, NSUserDomainMask, YES) lastObject];
    NSString *sqliteFilePath = [cachesPath stringByAppendingPathComponent:@"titleData.sqlist"];
    NSLog(@"数据库路径：%@", sqliteFilePath);
    int result = sqlite3_open([sqliteFilePath UTF8String], &dbA);
    if (result == SQLITE_OK) {
        
        NSLog(@"数据库打开成功");
        NSString *sql = @"create table titleData(titleArray data primary key not NULL)";
        char *error = nil;
        int splResult = sqlite3_exec(dbA, [sql UTF8String], NULL, NULL, &error);
        if (splResult == SQLITE_OK) {
            NSLog(@"标题表创建成功");
        }else{
            NSLog(@"标题表创建失败ERROR = %s", error);
        }
    }else
    {
        NSLog(@"标题数据库打开失败");
    }
    return dbA;
}

#pragma mark - 插入数组
- (void)insertTitleArray:(NSData*)array

{
    NSString *insertSQL = [NSString stringWithFormat:@"insert into titleData(titleArray) values(?)"];
    NSLog(@"insertSQL = %@", insertSQL);
    sqlite3_stmt *stmt = NULL;
    int result1 = sqlite3_prepare_v2(dbA, [insertSQL UTF8String], -1, &stmt, NULL);
    if (result1 == SQLITE_OK) {
        NSLog(@"标题语法检查正确");
        sqlite3_bind_blob(stmt, 1, [array bytes], (int)[array length], nil);
        sqlite3_step(stmt);
        NSLog(@"标题插入数据成功");
    }else{
        NSLog(@"标题语法检查错误");
    }
    
}

#pragma mark - 取出所有标题栏的数据
- (NSData*)searchAllTitle
{
    NSData *data = [NSData data];
    NSString *selectSQL = @"select * from titleData";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(dbA, [selectSQL UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        
        NSLog(@"标题语法检查正确");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            int bytes = sqlite3_column_bytes(stmt, 0);
            Byte * value = (Byte*)sqlite3_column_blob(stmt, 0);
            if (bytes !=0 && value != NULL)
            {
                
                data = [NSData dataWithBytes:value length:bytes];
                
            }
        }
        sqlite3_finalize(stmt);
        return data;
        
    }else{
        NSLog(@"标题语法检查错误");
        
    }
    // 9.释放所有权
    sqlite3_finalize(stmt);
    return nil;
    
}

#pragma mark - 更新标题栏标题
- (void)upDatesearchAllTitle:(NSData*)data
{
    NSString *updateSQL = [NSString stringWithFormat:@"update titleData set titleArray = '%@'", data];
    // 2.执行SQL语句
    sqlite3_stmt *stmt = NULL;
    char *error = nil;
    int result = sqlite3_prepare_v2(dbA, [updateSQL UTF8String], -1, &stmt, nil);
    // 3.判断SQL语句是否执行成功
    if (result == SQLITE_OK) {
        NSLog(@"标题更新成功");
    }else
    {
        NSLog(@"标题更新失败： %s", error);
    }
}

#pragma mark - 查询标题数据
- (BOOL)searchNews:(NSData*)data
{
    NSData *data1 = [NSData data];
    NSString *selectSQL = @"select * from titleData where titleArray = ?";
    sqlite3_stmt *stmt = NULL;
    int result = sqlite3_prepare_v2(dbA, [selectSQL UTF8String], -1, &stmt, NULL);
    if (result == SQLITE_OK) {
        sqlite3_bind_blob(stmt, 1, [data bytes], (int)[data length], nil);
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            int bytes = sqlite3_column_bytes(stmt, 0);
            Byte * value = (Byte*)sqlite3_column_blob(stmt, 0);
            if (bytes !=0 && value != NULL)
            {
                
                data1 = [NSData dataWithBytes:value length:bytes];
                if (data == data1) {
                    return YES;
                }
                
            }
            
            
            
            
        }
    }
    return NO;
}

#pragma mark - 删除数据
- (void)deleteAll
{
    
    // 1.删除的SQL语句(delete from 表名 where 字段 = '值')
    NSString *deleteSQL = [NSString stringWithFormat:@"delete from titleData"];
    // 2.执行SQL语句
    char *error = nil;
    int result = sqlite3_exec(dbA, [deleteSQL UTF8String], NULL, NULL, &error);
    // 3.判断SQL语句是否执行成功
    if (result == SQLITE_OK) {
        NSLog(@"删除成功");
    }else
    {
        NSLog(@"删除失败： %s", error);
    }
    
    
}

@end

@end
